USE [AMDB]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_BmwWelcomeScreen]    Script Date: 09/29/2014 15:32:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_BmwWelcomeScreen](@UnitId varchar(3),@OverDueLimit int,@AfterTimeLimit int)
RETURNS @retTable TABLE 
(
	
	CHECKINTIME datetime,
	CUSTFNAME nvarchar(255),
	CUSTLNAME nvarchar(255),
	ADVISORNAME nvarchar(255) NULL
)
as
BEGIN
insert into @retTable (ADVISORNAME,CHECKINTIME,CUSTFNAME,CUSTLNAME)
	select ADVISORNAME,CHECKINTIME,CUSTFNAME,CUSTLNAME from (
select d.NAME as ADVISORNAME,isnull(b.RECD,a.SERVD) as CHECKINTIME,
case when isnull(a.CONFNAME,'')+ISNULL(a.CONLNAME,'') ='' then c.FNAME else isnull(a.CONFNAME,'') end as CUSTFNAME,
case when isnull(a.CONFNAME,'')+ISNULL(a.CONLNAME,'') ='' then c.LNAME else isnull(a.CONLNAME,'') end as CUSTLNAME
from ALL_GSAL a 
left join ALL_GTRS b on a.GSALID = b.GSALID and a._UNITID = b._UNITID
left join CUST c on a.CUSTNO = c.CUSTNO
left join SMAN d on a.RECEIVER = d.SMANID
where 
a.STATUS not in ('M') -- not deleted
and a._UNITID=@UnitId and a.STYPE  in (select c1 from ALL_CORW where CODAID ='LIIKETAPTY' and C8 like '%B%')
)  t
where 
datediff(MINUTE,t.CHECKINTIME,DateAdd(minute,(-1)*@OverDueLimit,GETDATE()))<=0
and datediff(MINUTE,t.CHECKINTIME,DateAdd(minute,@AfterTimeLimit,GETDATE()))>=0
order by t.CHECKINTIME
	return;
END
GO


